/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package co.com.poligran.polla.mundialista.ingesoft.dao;

/**
 *
 * @author DAVID MOTTA
 */


import co.com.poligran.polla.mundialista.ingesoft.base.Mesa;
import static co.com.poligran.polla.mundialista.ingesoft.dao.Conexion.conexionSqlServer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;

/**
 *
 * @author DAVID MOTTA
 */
public class SqlServerMesaDAO implements MesaDAO {

	@Override
	public Collection selectMesa() {
		Mesa mesa = null;
		Conexion conexion = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		LinkedList selectUsuarios = null;
		StringBuilder sqlConsulta = new StringBuilder();
		try {
			conn = conexion.conexionSqlServer();
			sqlConsulta.append("SELECT idMesa, Monto, cuposDisponibles, estado, idSala, idPartido FROM dbo.mesa");
			ps = conn.prepareStatement(sqlConsulta.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			selectUsuarios = new LinkedList();
			while (rs.next()) {
				mesa = new Mesa();

				mesa.setIdMesa(rs.getString(1));
				mesa.setMonto(rs.getString(2));
				mesa.setCuposDisponibles(rs.getString(3));
				mesa.setEstado(rs.getString(4));
				mesa.setIdSala(rs.getString(5));
				mesa.setIdPartido(rs.getString(6));

				selectUsuarios.add(mesa);
			}

		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println(sqle.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException sqlers) {
				sqlers.printStackTrace();
				System.out.println("Resultset:");
				System.out.println(sqlers.getMessage());
			}
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException sqleps) {
				sqleps.printStackTrace();
				System.out.println("PreparedStatement:");
				System.out.println(sqleps.getMessage());
			}
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException sqleCon) {
				sqleCon.printStackTrace();
				System.out.println("Connection:");
				System.out.println(sqleCon.getMessage());
			}
		}

		return selectUsuarios;
	}

	@Override
	public boolean insertMesa(Mesa mesa) {
		boolean inserto = false;
		Connection conexion = conexionSqlServer();
		try {
			CallableStatement statement = (CallableStatement) conexion.prepareCall("EXEC sp_InsertMesa ?,?,?,?,?");

			statement.setString("Monto", mesa.getMonto());
			statement.setString("estado", mesa.getEstado());
			statement.setString("cuposDisponibles", mesa.getCuposDisponibles());
			statement.setString("idSala", mesa.getIdSala());
			statement.setString("idPartido", mesa.getIdPartido());

			statement.executeUpdate();

			inserto = true;
		} catch (Exception ex) {
			System.out.print("exepction  " + ex.getMessage());
			throw new Exception(ex.getMessage());
		} finally {
			return inserto;
		}
	}

	@Override
	public boolean updateMesa(Mesa mesa) {
		boolean inserto = false;
		Connection conexion = conexionSqlServer();
		try {
			CallableStatement statement = (CallableStatement) conexion.prepareCall("EXEC sp_UpdateMesa ?,?,?,?,?,?");

			statement.setString("idMesa", mesa.getIdMesa());
			statement.setString("Monto", mesa.getMonto());
			statement.setString("estado", mesa.getEstado());
			statement.setString("cuposDisponibles", mesa.getCuposDisponibles());
			statement.setString("idSala", mesa.getIdSala());
			statement.setString("idPartido", mesa.getIdPartido());

			statement.executeUpdate();

			inserto = true;
		} catch (Exception ex) {
			System.out.print("exepction  " + ex.getMessage());
			throw new Exception(ex.getMessage());
		} finally {
			return inserto;
		}
	}

	@Override
	public boolean deleteMesa(Mesa mesa) {
		boolean inserto = false;
		Connection conexion = conexionSqlServer();
		try {
			CallableStatement statement = (CallableStatement) conexion.prepareCall("EXEC sp_DeleteMesa ?");

			statement.setString("idMesa", mesa.getIdMesa());

			statement.executeUpdate();

			inserto = true;
		} catch (Exception ex) {
			System.out.print("exepction  " + ex.getMessage());
			throw new Exception(ex.getMessage());
		} finally {
			return inserto;
		}
	}

}
